package JDBC;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import pojo.Library;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class JDBCDemo {
    public static void main(String[] args) throws Exception {   //下面方法有不同的异常，我直接抛出一个大的异常
        Connection con = loadDriver();
        Scanner s = new Scanner(System.in);
        int num = -1;
        while (num!=0){
            printLibrary();
            num = s.nextInt();
            switch (num){
                case 0: {System.out.println("退出系统");break;}
                case 1: {
                    insert(con);
                    break;
                }
                case 2: {
                    delete(con);
                    break;
                }
                case 3: {
                    update(con);
                    break;
                }
                case 4: {
                    select(con);
                    break;
                }
                default:
                    System.out.println("无效指令");
            }
        }
        //释放链接
        con.close();
    }
// 打印菜单
    public static void printLibrary() {
        System.out.println("\n=============================欢迎进入图书馆管理系统，请问你需要进行什么操作=============================");
        System.out.println("0:退出系统");
        System.out.println("1:增加图书");
        System.out.println("2:删除图书");
        System.out.println("3:修改图书");
        System.out.println("4:查看图书");
        System.out.print("请输入操作编号:");
    }
    /**
     * 删除图书
     * @param con
     */
    private static void delete(Connection con) throws Exception{
        Scanner s = new Scanner(System.in);
        System.out.println("请输入你要删除的id");
        Integer id = s.nextInt();
        //定义sql语句
        String sql = "delete from booklibrary where id = ?";

        // 获取PreparedStatement并设置参数
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setInt(1,id);

        // 执行插入
        int count = pstmt.executeUpdate();

        //处理结果
        System.out.println(count > 0);

        //释放
        s.close();
    }

    /**
     * 修改图书
     *
     * @param con
     */
    private static void update(Connection con) throws Exception {
        Scanner s = new Scanner(System.in);
        System.out.println("请输入你要修改的图书ID:");
        Integer id = s.nextInt();
        System.out.println("请输入书名");
        String name = s.next();
        System.out.println("请选择类型：0：文学类，1：自然科学类，2：教辅类，3：儿童读物");
        String type = s.next();
        System.out.println("请输入描述：");
        String description = s.next();
        Date date = new Date();

        //定义sql语句
        String sql = "update booklibrary set name = ?,type = ?,description = ?,update_time = ? where id = ?";

        // 获取PreparedStatement并设置参数
        try (PreparedStatement pstmt = con.prepareStatement(sql)) {
            pstmt.setString(1, name);          // 设置书名
            pstmt.setString(2, type);          // 设置类型
            pstmt.setString(3, description);   // 设置描述

            // 将java.util.Date转换为java.sql.Timestamp（适配数据库DATETIME/TIMESTAMP）
            Timestamp updateTime = new Timestamp(date.getTime());
            pstmt.setTimestamp(4, updateTime);  // 设置update_time
            pstmt.setInt(5, id);

            // 执行插入
            int count = pstmt.executeUpdate();

            //处理结果
            System.out.println(count > 0);
        }
        s.close();
    }

    /**
     * 加载驱动
     *
     * @return
     * @throws Exception
     */
    public static Connection loadDriver() throws Exception {
        //1.加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));

        //2.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //3.获取数据库链接Connection
        Connection con = dataSource.getConnection();

        return con;
    }

    /**
     * 查询图书管理系统
     *
     * @param con
     * @throws Exception
     */
    public static void select(Connection con) throws Exception {
        //4、定义sql语句
        String sql = "select * from booklibrary";

        //5、获取执行sql语句的对象
        Statement book = con.createStatement();

        //6、执行sql并接收返回结果
        ResultSet select = book.executeQuery(sql);

        List<Library> libray = new ArrayList<>();
        Library booklibrary = null;
        //7、 处理结果集
        while (select.next()) {
            Integer id = select.getInt("id");
            String name = select.getString("name");
            Short typeInt = (short) select.getInt("type");
            String description = select.getString("description");
            Date createTime = select.getTime("create_time");
            Date updateTime = select.getTime("update_time");

            //封装对象
            booklibrary = new Library();
            booklibrary.setId(id);
            booklibrary.setName(name);
            booklibrary.setType(typeInt);
            booklibrary.setDescription(description);
            booklibrary.setCreateTime(createTime);
            booklibrary.setUpdateTime(updateTime);

            //装载集合
            libray.add(booklibrary);
            System.out.println(booklibrary);
        }
        //8、释放资源
        book.close();
        select.close();
    }

    /**
     * 添加图书系统
     *
     * @param con
     * @throws Exception
     */
    public static void insert(Connection con) throws Exception {
        Scanner s = new Scanner(System.in);
        System.out.println("请输入书名");
        String name = s.next();
        System.out.println("请选择类型：0：文学类，1：自然科学类，2：教辅类，3：儿童读物");
        String type = s.next();
        System.out.println("请输入描述：");
        String description = s.next();
        Date date = new Date();

        //定义sql语句
        String sql = "insert into booklibrary(name, type, description, create_time, update_time) values (?,?,?,?,?)";

        // 获取PreparedStatement并设置参数
        try (PreparedStatement pstmt = con.prepareStatement(sql)) {
            pstmt.setString(1, name);          // 设置书名
            pstmt.setString(2, type);          // 设置类型
            pstmt.setString(3, description);   // 设置描述

            // 将java.util.Date转换为java.sql.Timestamp（适配数据库DATETIME/TIMESTAMP）
            Timestamp createTime = new Timestamp(date.getTime());
            Timestamp updateTime = new Timestamp(date.getTime());

            pstmt.setTimestamp(4, createTime);  // 设置create_time
            pstmt.setTimestamp(5, updateTime);  // 设置update_time

            // 执行插入
            int count = pstmt.executeUpdate();

            //处理结果
            System.out.println(count > 0);
        }
        s.close();
    }
}
